package exam;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class MySqlExamresultDao implements ExamresultDao {

	@Override
	public int add(Examresult e) {
		String sql = "insert into `examresult`(exam_date,score,st_id,sb_id) values (?,?,?,?)";
		return DBUtil.executeUpdate(sql, e.getExamdate(), e.getScore(),
				e.getStid(), e.getSbid());
	}

	@Override
	public int del(Long id) {
		String sql = "delete from examresult where id=?";
		return DBUtil.executeUpdate(sql, id);
	}

	@Override
	public int update(Examresult e) {
		String sql = "update examresult set exam_date=?,score=?,st_id=?,sb_id=? where id=?";
		return DBUtil.executeUpdate(sql, e.getExamdate(), e.getScore(),
				e.getStid(), e.getSbid(), e.getId());
	}

	@Override
	public Examresult find(Long id) {
		Examresult e = null;
		PreparedStatement st = null;
		ResultSet rs = null;
		String sql = "select * from `examresult` where id=?";
		Connection conn = null;
		try {
			conn = DBUtil.getConnection();
			st = conn.prepareStatement(sql);
			st.setLong(1, id);
			rs = st.executeQuery();
			if (rs.next()) {
				e = new Examresult();
				e.setId(rs.getLong("id"));
				e.setExamdate(rs.getDate("exam_date"));
				e.setScore(rs.getDouble("score"));
				e.setStid(rs.getLong("st_id"));
				e.setSbid(rs.getLong("sb_id"));
			}
		} catch (SQLException ex) {
			// TODO Auto-generated catch block
			ex.printStackTrace();
		} finally {
			DBUtil.close(rs, st, conn);
		}
		return e;
	}

	@Override
	public List<Examresult> query() {
		List<Examresult> list = new ArrayList<Examresult>();
		Examresult e = null;
		PreparedStatement st = null;
		ResultSet rs = null;
		String sql = "select * from `examresult`";
		Connection conn = null;
		try {
			conn = DBUtil.getConnection();
			st = conn.prepareStatement(sql);
			rs = st.executeQuery();
			while (rs.next()) {
				e = new Examresult();
				e.setId(rs.getLong("id"));
				e.setExamdate(rs.getDate("exam_date"));
				e.setScore(rs.getDouble("score"));
				e.setStid(rs.getLong("st_id"));
				e.setSbid(rs.getLong("sb_id"));
				list.add(e);
			}
		} catch (SQLException ex) {
			// TODO Auto-generated catch block
			ex.printStackTrace();
		} finally {
			DBUtil.close(rs, st, conn);
		}
		return list;
	}

}
